import csv
import logging
import os
import sqlite3
from datetime import datetime

import pandas as pd
from PySide6.QtCore import QAbstractTableModel, QEvent, QModelIndex, Qt, Signal
from PySide6.QtGui import QBrush, QColor
from PySide6.QtWidgets import (
    QApplication,
    QComboBox,
    QMessageBox,
    QStyle,
    QStyledItemDelegate,
    QStyleOptionButton,
)

from src.models.student import Student

logger = logging.getLogger(__name__)

STATUS_COLORS = {
    "是": QColor(220, 255, 220),
    "否": QColor(255, 255, 200),
    "非团员": QColor(220, 220, 255),
}

# 确保"操作"放在最后一列
HEADERS_INDEX = {
    "学号": 0,
    "姓名": 1,
    "团籍档案是否收到": 2,
    "学籍档案是否收到": 3,
    "最后更新时间": 4,
    "操作": 5,
}


class StudentTableModel(QAbstractTableModel):
    def __init__(self, db_name="students.db"):
        super().__init__()
        self.db_path = os.path.join("data", db_name)
        if not os.path.exists("data"):
            os.makedirs("data")
        self._data = []
        self._headers = list(HEADERS_INDEX.keys())

        self.init_database()
        self.load_data()

    def init_database(self):
        """初始化SQLite数据库和表结构"""
        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("""
                    CREATE TABLE IF NOT EXISTS students (
                        id TEXT PRIMARY KEY,
                        name TEXT NOT NULL,
                        is_tuanyuan_archives_received TEXT NOT NULL,
                        is_student_record_received TEXT NOT NULL,
                        last_update TEXT NOT NULL  -- ISO格式字符串
                    )
                """)
        except Exception as e:
            QMessageBox.critical(None, "数据库错误", f"初始化数据库失败: {str(e)}")

    def rowCount(self, parent=QModelIndex()):
        return len(self._data)

    def columnCount(self, parent=QModelIndex()):
        return len(self._headers)

    def data(self, index, role=Qt.DisplayRole):
        if not index.isValid():
            return None

        row = self._data[index.row()]
        column = index.column()

        if column == HEADERS_INDEX["操作"]:  # 操作列
            return None

        if role == Qt.DisplayRole or role == Qt.EditRole:
            if column == HEADERS_INDEX["最后更新时间"]:  # last_update
                return row[column].strftime("%Y-%m-%d %H:%M:%S")
            return row[column]

        # 设置单元格背景色
        if role == Qt.BackgroundRole:
            if column in (
                HEADERS_INDEX["团籍档案是否收到"],
                HEADERS_INDEX["学籍档案是否收到"],
            ):  # 状态列
                value = row[column]
                if value in STATUS_COLORS:
                    return QBrush(STATUS_COLORS[value])

        return None

    def setData(self, index, value, role=Qt.EditRole):
        if not index.isValid() or role != Qt.EditRole:
            return False

        row = index.row()
        column = index.column()
        student_id = self._data[row][HEADERS_INDEX["学号"]]
        old_value = self._data[row][column]

        if old_value != value:
            # 更新数据库
            success = self.update_student_in_db(student_id, column, value)
            if not success:
                return False

            # 更新内存中的数据
            self._data[row][HEADERS_INDEX["最后更新时间"]] = datetime.now()
            self._data[row][column] = value
            logger.info(
                f"数据更新：学生ID: {student_id}, 列名: {self._headers[column]}, 旧值: {old_value}, 新值: {value}"
            )
            # 通知所有关联的proxy model数据已变更
            # self.dataChanged.emit(
            #     self.index(row, 0),  # 起始索引
            #     self.index(row, self.columnCount() - 1),  # 结束索引
            #     [Qt.DisplayRole, Qt.EditRole, Qt.BackgroundRole],  # 需要更新的角色
            # )
        return True

    def headerData(self, section, orientation, role=Qt.DisplayRole):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            if 0 <= section < len(self._headers):
                return self._headers[section]
        return None

    def flags(self, index):
        if not index.isValid():
            return Qt.NoItemFlags

        flags = Qt.ItemIsEnabled | Qt.ItemIsSelectable

        column = index.column()
        # 除了最后更新时间列，其他都可编辑
        if column != HEADERS_INDEX["最后更新时间"] and column != HEADERS_INDEX["操作"]:
            flags |= Qt.ItemIsEditable

        return flags

    def load_data(self):
        """从SQLite加载数据"""
        try:
            self.beginResetModel()
            self._data = []

            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT * FROM students")
                rows = cursor.fetchall()

                for row in rows:
                    # 转换数据类型
                    (
                        student_id,
                        name,
                        archives_status,
                        record_status,
                        last_update,
                    ) = row
                    self._data.append(
                        [
                            student_id,
                            name,
                            archives_status,
                            record_status,
                            datetime.fromisoformat(last_update),
                        ]
                    )
                logger.info(f"加载数据成功，加载 {len(rows)} 条记录")

            self.endResetModel()
        except Exception as e:
            QMessageBox.critical(None, "错误", f"加载数据失败: {str(e)}")

    def update_student_in_db(self, student_id, column, value):
        """更新数据库中的学生记录"""
        column_names = [
            "id",
            "name",
            "is_tuanyuan_archives_received",
            "is_student_record_received",
            "last_update",
        ]
        column_name = column_names[column]

        try:
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()

                # 处理日期时间
                if column_name == "last_update":
                    value = datetime.now().isoformat()

                # 构建SQL语句
                sql = f"""UPDATE students 
                          SET {column_name} = ?, 
                              last_update = ? 
                          WHERE id = ?"""
                current_time = datetime.now().isoformat()
                cursor.execute(sql, (value, current_time, student_id))
                if cursor.rowcount == 0:
                    QMessageBox.warning(
                        None, "警告", f"未找到ID为{student_id}的学生记录"
                    )
                    return False
            return True
        except Exception as e:
            logger.error(
                f"更新数据库失败: {str(e)}, sql: {sql}, column: {column_name}, value: {value}"
            )
            QMessageBox.critical(None, "错误", f"更新数据库失败: {str(e)}")
            return False

    def add_student(self, student: Student, quiet=False):
        """添加单个学生到数据库和表格"""
        return self.add_students([student], quiet)

    def add_students(self, students: list[Student], quiet=False):
        """批量添加多个学生到数据库和表格"""
        if not students:
            return False

        try:
            self.beginInsertRows(
                QModelIndex(), self.rowCount(), self.rowCount() + len(students) - 1
            )

            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 使用executemany批量插入
                cursor.executemany(
                    """INSERT INTO students 
                       (id, name, is_tuanyuan_archives_received, is_student_record_received, last_update)
                       VALUES (?, ?, ?, ?, ?)""",
                    [
                        (
                            s.id,
                            s.name,
                            s.is_tuanyuan_archives_received,
                            s.is_student_record_received,
                            s.last_update.isoformat(),
                        )
                        for s in students
                    ],
                )

            # 批量添加到内存数据
            for student in students:
                self._data.append(
                    [
                        student.id,
                        student.name,
                        student.is_tuanyuan_archives_received,
                        student.is_student_record_received,
                        student.last_update,
                    ]
                )

            logger.info(f"成功添加 {len(students)} 条学生记录")
            self.endInsertRows()
            return True
        except Exception as e:
            if not quiet:
                QMessageBox.critical(None, "错误", f"添加学生失败: {str(e)}")
            logger.error(f"添加学生失败: {str(e)}")
            self.beginResetModel()  # 回滚插入操作
            self.endResetModel()
            return False

    def delete_student(self, row):
        """从数据库和表格中删除单个学生"""
        if row < 0 or row >= self.rowCount():
            return False

        student_id = self._data[row][HEADERS_INDEX["学号"]]
        return self._delete_students_by_ids([student_id], [row])

    def delete_students(self, row_indices):
        """从数据库和表格中批量删除多个学生"""
        if not row_indices:
            return

        # 获取要删除的学生ID列表和行号列表
        student_ids = [
            self._data[row][HEADERS_INDEX["学号"]]
            for row in row_indices
            if 0 <= row < self.rowCount()
        ]
        rows = sorted(row_indices, reverse=True)

        return self._delete_students_by_ids(student_ids, rows)

    def _delete_students_by_ids(self, student_ids, rows):
        """内部方法：根据ID列表批量删除学生"""
        if not student_ids:
            return False

        try:
            # 批量删除数据库记录
            with sqlite3.connect(self.db_path) as conn:
                cursor = conn.cursor()
                # 使用IN语句一次性删除多个记录
                placeholders = ",".join(["?"] * len(student_ids))
                cursor.execute(
                    f"DELETE FROM students WHERE id IN ({placeholders})", student_ids
                )

            # 批量删除内存数据
            self.beginRemoveRows(QModelIndex(), rows[-1], rows[0])
            for row in rows:
                del self._data[row]
            self.endRemoveRows()
            logger.info(f"成功删除 {len(student_ids)} 条学生记录")
            return True
        except Exception as e:
            QMessageBox.critical(None, "错误", f"删除学生失败: {str(e)}")
            self.beginResetModel()  # 回滚删除操作
            self.endResetModel()
            return False

    def sort(self, column, order):
        """实现排序功能"""
        if column == HEADERS_INDEX["操作"]:
            return
        self.layoutAboutToBeChanged.emit()

        # 根据列类型选择排序键
        if column == HEADERS_INDEX["最后更新时间"]:
            self._data.sort(
                key=lambda x: x[column], reverse=order == Qt.DescendingOrder
            )
        else:  # 其他列按字符串排序
            self._data.sort(
                key=lambda x: str(x[column]), reverse=order == Qt.DescendingOrder
            )

        self.layoutChanged.emit()

    def export_data(self, file_path: str):
        """导出数据到CSV文件"""
        try:
            if file_path.endswith(".csv"):
                with open(
                    file_path, mode="w", newline="", encoding="utf-8-sig"
                ) as file:
                    writer = csv.writer(file)
                    writer.writerow(self._headers[:-1])
                    for row in self._data:
                        writer.writerow(row)
                    logger.info(f"数据已成功导出到 {file_path}")
            elif file_path.endswith(".xlsx"):
                df = pd.DataFrame(self._data, columns=self._headers[:-1])
                df.to_excel(file_path, index=False, engine="openpyxl")
                QMessageBox.information(None, "提示", f"数据已成功导出到 {file_path}")
                logger.info(f"数据已成功导出到 {file_path}")
            else:
                raise ValueError("不支持的文件格式")
        except Exception as e:
            QMessageBox.critical(None, "错误", "导出数据失败")
            logger.error(f"导出数据失败: {str(e)}")

    def import_data(self, file_path: str):
        """导入CSV或Excel文件数据"""
        try:
            file = None
            # 根据文件扩展名选择不同的读取方式
            if file_path.endswith(".csv"):
                file = open(file_path, mode="r", newline="", encoding="utf-8-sig")
                reader = csv.reader(file)
                headers = next(reader)
            else:
                df = pd.read_excel(file_path, engine="openpyxl")
                headers = df.columns.tolist()

            # 验证表头
            expected_headers = self._headers[:-1]
            if headers != expected_headers:
                logger.error(f"表头与预期不一致: {headers}, 预期: {expected_headers}")
                raise ValueError("表头与预期不一致")

            # 读取数据行
            if file_path.endswith(".csv"):
                rows = list(reader)
            else:
                rows = df.values.tolist()

            # 批量读取所有学生数据
            students = [
                Student(
                    id=row[HEADERS_INDEX["学号"]],
                    name=row[HEADERS_INDEX["姓名"]],
                    is_tuanyuan_archives_received=row[
                        HEADERS_INDEX["团籍档案是否收到"]
                    ],
                    is_student_record_received=row[HEADERS_INDEX["学籍档案是否收到"]],
                    last_update=datetime.now(),
                )
                for row in rows
            ]

            # 批量添加学生
            success = self.add_students(students, quiet=True)
            total = len(students)

            if success:
                logger.info(f"数据导入成功，共导入 {total} 条记录")
                QMessageBox.information(
                    None, "提示", f"数据导入成功，共导入 {total} 条记录"
                )
                return True
            else:
                raise RuntimeError("添加学生失败")

        except ImportError:
            QMessageBox.critical(
                None,
                "依赖缺失",
                "导入Excel需要安装pandas和openpyxl，请运行'pip install pandas openpyxl'",
            )
            return False
        except Exception as e:
            QMessageBox.critical(None, "错误", f"导入数据失败: {str(e)}")
            return False
        finally:
            if file:
                file.close()


class StatusDelegate(QStyledItemDelegate):
    # 统一颜色规则（让单元格和下拉框用同一套逻辑）

    def __init__(self, parent=None):
        super().__init__(parent)
        self.options = {
            HEADERS_INDEX["团籍档案是否收到"]: ["是", "否", "非团员"],
            HEADERS_INDEX["学籍档案是否收到"]: ["是", "否"],
        }

    def createEditor(self, parent, option, index):
        column = index.column()
        if column in self.options:
            editor = QComboBox(parent)
            editor.addItems(self.options[column])

            # 为每个选项设置普通状态颜色
            for i, text in enumerate(self.options[column]):
                if text in STATUS_COLORS:
                    base_color = STATUS_COLORS[text]
                    # 设置普通状态的背景色
                    editor.setItemData(i, QBrush(base_color), Qt.BackgroundRole)

            # 优化下拉框样式
            editor.setStyleSheet("""
                QComboBox QAbstractItemView {
                    selection-background-color: rgba(100, 100, 100, 255);
                    selection-color: black;
                }
            """)

            return editor
        return super().createEditor(parent, option, index)


class ButtonDelegate(QStyledItemDelegate):
    buttonClicked = Signal(int)

    def __init__(self, parent=None):
        super().__init__(parent)
        self._pressed = None

    def paint(self, painter, option, index):
        """绘制按钮"""
        try:
            opt = QStyleOptionButton()
            opt.rect = option.rect
            opt.text = "选中"
            opt.state = QStyle.State_Enabled | QStyle.State_Raised

            # 处理按钮按下状态
            if self._pressed == index:
                opt.state |= QStyle.State_Sunken

            QApplication.style().drawControl(QStyle.CE_PushButton, opt, painter)
        except Exception as e:
            logger.error(f"paint方法异常: {str(e)}")

    def editorEvent(self, event, model, option, index):
        """处理编辑器事件"""
        try:
            if not index.isValid():
                return False

            # 处理鼠标按下事件
            if event.type() == QEvent.MouseButtonPress:
                if event.button() == Qt.LeftButton:
                    self._pressed = index
                    return True
            # 处理鼠标释放事件
            elif event.type() == QEvent.MouseButtonRelease:
                if event.button() == Qt.LeftButton and self._pressed == index:
                    self.buttonClicked.emit(index.row())
                    self._pressed = None
                    return True
                self._pressed = None
            return super().editorEvent(event, model, option, index)
        except Exception as e:
            logger.error(f"editorEvent异常: {str(e)}")
            return False

    def createEditor(self, parent, option, index):
        """禁用编辑功能"""
        return None
